import  pymysql

class MysqlCustom:
    #1，建立链接的配置信息
    def __init__(self,host,user,password,database,port=3306,charset='utf8'):
        self.host=host
        self.user=user
        self.password=password
        self.database=database
        self.port=port
        self.charset=charset


    #2,建立链接方法,创建游标
    def connect_func(self):
        try:
            print('开始建立Connect对象：')
            self.connect=pymysql.connect(
                host = self.host,
                user = self.user,
                password = self.password,
                database = self.database,
                port = self.port,
                charset =self.charset)

            print('建立数据库Connect对象成功！')
        except Exception as e:
            print('建立数据库Connect对象失败：',e)


        try:
            #创建游标
            self.cursor = self.connect.cursor()
        except Exception as e:
            print('建立游标失败：', e)

    #3关闭游标，关闭链接
    def close_func(self):
        try:
            print('开始关闭游标##ing')
            self.cursor.close()
            print('关闭游标成功')
        except Exception as e:
            print('关闭游标失败：',e)

        try:
            print('开始关闭链接##ing')
            self.connect.close()
            print('关闭链接成功!')
        except Exception as e:
            print('关闭链接失败：',e)


    #3,查询封装
    def select_func(self,sql):
        #执行查询语句
        try:
            self.connect_func()
            self.cursor.execute(sql)
            result=self.cursor.fetchall()
            print('#####################################')
            print('查询执行成功,结果如下：')
            for data in result:
                print(data)
            print('#####################################')
            self.close_func()

        except Exception as e:
            print('查询失败：', e)
            self.close_func()

    #4增，删，改 封装方法
    def __edit(self,sql):
        #建立链接
        self.connect_func()
        try:
            num = self.cursor.execute(sql)
            self.connect.commit()
            print('执行语句影响行数：',num)
        except Exception as e:
            print('执行sql失败，开始回滚ing:',e)
            self.connect.rollback()
            print('回滚完成，done')

        #关闭链接
        self.close_func()

    #5,新增
    def insert(self,sql):
        self.__edit(sql)
    #6，修改
    def update(self,sql):
        self.__edit(sql)
    #7，删除
    def delete(self,sql):
        self.__edit(sql)


if __name__ == '__main__':
    A=MysqlCustom(
        host='114.116.72.149',
        user='root',
        password='Xiaohao@5433',
        database='fzulab'

    )
    A.select_func('select * from biz_article  order by create_date desc limit 10')

    # A.update('update kn_question set question="站点管理员15" where id=68461')
